/**
 * 
 */
package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.order.DeliveryDetails;
import com.xbang.attendance.entity.order.DeliveryOrder;
import com.xbang.attendance.entity.order.PurchaseDetails;
import com.xbang.attendance.entity.order.PurchaseOrder;
import com.xbang.attendance.entity.stock.Inventory;
import com.xbang.attendance.entity.stock.InventoryExchange;
import com.xbang.attendance.entity.stock.InventoryRecord;
import com.xbang.attendance.entity.system.MaintenanceStation;
import com.xbang.attendance.entity.system.SysParam;
import com.xbang.attendance.entity.system.Warehouse;
import com.xbang.attendance.entity.system.WarehouseProductAgreement;
import com.xbang.attendance.security.support.SecurityUserHolder;
import com.xbang.attendance.service.DeliveryOrderService;
import com.xbang.attendance.service.InventoryExchangeService;
import com.xbang.attendance.service.InventoryService;
import com.xbang.attendance.service.MailService;
import com.xbang.attendance.service.MaintenanceStationService;
import com.xbang.attendance.service.ProductService;
import com.xbang.attendance.service.PurchaseOrderService;
import com.xbang.attendance.service.WarehouseService;
import com.xbang.attendance.utils.DateUtil;
import com.xbang.attendance.utils.DeliveryJsonObject;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.attendance.utils.HSSFUtil;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;

import edu.emory.mathcs.backport.java.util.Collections;
import enumeration.DeliveryStatus;
import enumeration.InventoryOperation;

/**
 * @author Administrator
 * 
 */
@Service("deliveryOrderService")
public class DeliveryOrderServiceImpl extends BaseServiceImpl implements DeliveryOrderService {

	@Autowired
	private MaintenanceStationService maintenanceStationService;
	@Autowired
	private ProductService productService;
	@Autowired
	private WarehouseService warehouseService;
	@Autowired
	private InventoryService inventoryService;
	@Autowired
	private PurchaseOrderService purchaseOrderService;
	@Autowired
	private InventoryExchangeService inventoryExchangeService;

	@Autowired
	private MailService mailService;

	@Autowired
	private GenericDaoSupport genericDaoSupport;

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.DeliveryOrderService#saveDeliveryOrder(java.lang.Integer, java.lang.Integer,
	 * java.lang.Integer, java.lang.Integer[])
	 */
	@Override
	public void saveDeliveryOrder(List<DeliveryJsonObject> lists) throws Exception {
		
		DeliveryOrder deliveryOrder = new DeliveryOrder();
		PurchaseOrder purchaseOrder = purchaseOrderService.getPurchaseOrderByKey(lists.get(0).getPid());
		MaintenanceStation station = maintenanceStationService.getMaintenanceStationById(lists.get(0).getMaintenanceStationId());
		Set<Warehouse> waSet = new HashSet<Warehouse>();

		for (DeliveryJsonObject deliveryJsonObject : lists) {
			Warehouse house = warehouseService.getWarehouse(deliveryJsonObject.getWarehouseId());
			waSet.add(house);
		}

		deliveryOrder.initDelivery(purchaseOrder, station, station.getMainWarehouse(), waSet, lists.get(0).getDoNo());
		
		//手动关联
		deliveryOrder.setManualGeneric(true);
		// 保存送货单
		save(deliveryOrder);

		//		
		purchaseOrder.getDeliveryOrders().add(deliveryOrder);
		//修改百分比
		purchaseOrder.setPercentage(new BigDecimal(100));

		// PO关联DO单
		save(purchaseOrder);

		Map<Integer, DeliveryDetails> maps = new HashMap<Integer, DeliveryDetails>();
		Set<DeliveryDetails> detSet = new HashSet<DeliveryDetails>();

		// 保存子单
		for (DeliveryJsonObject deliveryJsonObject : lists) {

			DeliveryDetails deliveryDetails = null;
			PurchaseDetails details = (PurchaseDetails) get(PurchaseDetails.class, deliveryJsonObject.getId());

			//
			if (!maps.containsKey(deliveryJsonObject.getId())) {// 是否是跟新
				deliveryDetails = new DeliveryDetails();
				deliveryDetails.setProduct(details.getProduct());
				deliveryDetails.setProductNumber(deliveryJsonObject.getAm());
				deliveryDetails.setDeliveryOrder(deliveryOrder);
				detSet.add(deliveryDetails);
				// deliveryOrder.getDeliveryDetails().add(deliveryDetails);

			} else {
				deliveryDetails = maps.get(deliveryJsonObject.getId());
				deliveryDetails.setProductNumber(deliveryDetails.getProductNumber().add(deliveryJsonObject.getAm()));

			}
			// 保持DO明细
			saveOrUpdate(deliveryDetails);
			maps.put(deliveryJsonObject.getId(), deliveryDetails);
			/**借货人的库存*/
			Inventory inventory = (Inventory) get(Inventory.class, deliveryJsonObject.getInvenId());
			/**借货人的库存*/
			Warehouse warehouse = (Warehouse) get(Warehouse.class, deliveryJsonObject.getWarehouseId());

			// //拿到本身的库存记录
			Inventory tmpInventory = inventoryService.getInventoryByParameters(deliveryOrder.getWarehouse(), details.getProduct());
			//WarehouseProductAgreement warehouseProductAgreement = inventoryService.getWarehouseProductAgreementByParameter(warehouse, details.getProduct());
//			if (tmpInventory == null) {
//				// //增加库存信息
//
//				tmpInventory = new Inventory();
//				tmpInventory.initial(deliveryOrder.getWarehouse(), details.getProduct());
//				tmpInventory.setExchangeInventory(new BigDecimal(0).subtract(deliveryJsonObject.getAm()));
//				if (warehouseProductAgreement == null) {
//					tmpInventory.setAgreementAmount(new BigDecimal(0));
//				} else {
//					tmpInventory.setAgreementAmount(warehouseProductAgreement.getNumber());
//				}
//
//				inventoryService.save(tmpInventory);
//
//				inventoryService.updateInventoryWarning(tmpInventory);
//			} else {
//				if (warehouseProductAgreement == null) {
//					tmpInventory.setAgreementAmount(new BigDecimal(0));
//				} else {
//					tmpInventory.setAgreementAmount(warehouseProductAgreement.getNumber());
//				}
				//BigDecimal exchangeInventoryAmount = inventory.getExchangeInventory() == null ? new BigDecimal(0) : inventory.getExchangeInventory();

				//inventory.setExchangeInventory(exchangeInventoryAmount.subtract(deliveryJsonObject.getAm()));
				//inventoryService.update(tmpInventory);
				//inventoryService.updateInventoryWarning(tmpInventory);
		//	}
			
		//	inventory.setAmount(inventory.getAmount().subtract(deliveryJsonObject.getAm()));
			
			
			details.setBalanceNumber(details.getBalanceNumber().subtract(deliveryJsonObject.getAm()));
			details.setDeliveryNumber(details.getDeliveryNumber().add(deliveryJsonObject.getAm()));
			// 更新PO明细
			save(details);

			inventory.setAmount(inventory.getAmount().subtract(deliveryJsonObject.getAm()));
			inventory.setExchangeInventory(inventory.getExchangeInventory().add(deliveryJsonObject.getAm()));
			
			inventory.setOutAmount(inventory.getOutAmount().add(deliveryJsonObject.getAm()));
			
			inventoryService.updateInventoryWarning(inventory);
			inventoryService.updateInventoryWarning(tmpInventory);

			// 更新库存
			save(inventory);

			// 调出记录
			InventoryExchange inInventoryExchange = new InventoryExchange();
			inInventoryExchange.initial(deliveryOrder.getWarehouse(), warehouse, details.getProduct(), deliveryJsonObject.getAm(), "out",
					InventoryOperation.TURE_OUT);
			save(inInventoryExchange);

			// 调入记录
			InventoryExchange outInventoryExchange = new InventoryExchange();
			outInventoryExchange.initial(warehouse, deliveryOrder.getWarehouse(), details.getProduct(), deliveryJsonObject.getAm(), "in",
					InventoryOperation.TRANSFERRED);
			save(outInventoryExchange);

			//调出记录
			InventoryRecord inventoryRecord = new InventoryRecord().initialInventoryRecord(warehouse, details.getProduct(), InventoryOperation.TURE_OUT,
					deliveryJsonObject.getAm(), SecurityUserHolder.getCurrentUser().getUsername());
			
			//tmpInventory = tmpInventory == null ? inventory : tmpInventory;
			
			inventoryRecord.setInventory(inventory);
			inventoryRecord.setDeliveryOrder(deliveryOrder);
			inventoryRecord.setDeliveryDetails(deliveryDetails);
			inventoryRecord.setPurchaseOrder(purchaseOrder);
			//设置备用
			inventoryRecord.setWare(deliveryOrder.getWarehouse());
			
			// 保存调出记录
			save(inventoryRecord);
			
			//调入记录
			InventoryRecord record = new InventoryRecord().initialInventoryRecord(deliveryOrder.getWarehouse(), details.getProduct(), InventoryOperation.TRANSFERRED,
					deliveryJsonObject.getAm(), SecurityUserHolder.getCurrentUser().getUsername());
			
			//tmpInventory = tmpInventory == null ? inventory : tmpInventory;
			
			record.setWare(warehouse);
			record.setInventory(inventory);
			record.setDeliveryOrder(deliveryOrder);
			record.setDeliveryDetails(deliveryDetails);
			record.setPurchaseOrder(purchaseOrder);
			// 保存调入记录
			save(record);
			
		}
	//	deliveryOrder = (DeliveryOrder) get(DeliveryOrder.class, deliveryOrder.getId());
	}

	@Override
	public PurchaseOrder getOrderByKey(Integer id) throws Exception {
		PurchaseOrder order = purchaseOrderService.getPurchaseOrderByKey(id);
		Set<Warehouse> warehouses = order.getMaintenanceStation().getWarehouse();
		List<Warehouse> warehouses2 = new ArrayList<Warehouse>();
		for (Warehouse warehouse : warehouses) {
			if (!inventoryService.warehouseIsExist(warehouse)) {
				warehouses2.add(warehouse);
			}
		}
		order.getMaintenanceStation().getWarehouse().removeAll(warehouses2);
		return order;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.xbang.attendance.service.DeliveryOrderService#updateDeliveryStatus(com.xbang.attendance.entity.order.DeliveryOrder
	 * , enumeration.DeliveryStatus, java.sql.Date, java.lang.String)
	 */
	public void updateDeliveryStatus(DeliveryOrder deliveryOrder, DeliveryStatus deliveryStatus, Date updateDate, String updater) throws Exception {

		String personStatus = deliveryOrder.getUpdateDeliveryStatusPersonFieldName(deliveryStatus);
		String dateStatus = deliveryOrder.getUpdateDeliveryStatusDateFieldName(deliveryStatus);

		if (null != personStatus && null != dateStatus) {

			String sqlSentence = "UPDATE delivery_order SET delivery_status = :devlieryStatus ,  ".concat(personStatus).concat(" = :person , ").concat(
					dateStatus).concat(" = :date WHERE id = :id");

			Map<String, Object> parameters = new HashMap<String, Object>();

			parameters.put("person", updater);
			parameters.put("date", updateDate);
			parameters.put("devlieryStatus", deliveryOrder.getDeliveryStatusString(deliveryStatus));
			parameters.put("id", deliveryOrder.getId());

			execute(sqlSentence, parameters);
		}

	}

	@Override
	public DeliveryOrder getDeliveryOrderByPurchase(PurchaseOrder purchaseOrder) {
		String sentence = " FROM DeliveryOrder delivery WHERE delivery.purchaseOrder = :purchaseOrder";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("purchaseOrder", purchaseOrder);

		List objects = genericDaoSupport.searchForList(sentence, parameters);

		if (null != objects && !objects.isEmpty()) {
			return (DeliveryOrder) objects.get(0);
		}
		return null;
	}
	/*
	 * (non-Javadoc)
	 * @see com.xbang.attendance.service.DeliveryOrderService#sendMail(java.lang.Integer)
	 */
	@Override
	public void sendMail(String webPath,String templatePath,String [] ids) throws Exception {
		
		Map<Integer,List<DeliveryOrder>> map = new HashMap<Integer, List<DeliveryOrder>>();
		
		for (String id : ids) {
			Integer inId = Integer.parseInt(id);
			DeliveryOrder deliveryOrder = (DeliveryOrder) get(DeliveryOrder.class,inId);
			Warehouse warehouse = deliveryOrder.getWarehouse();
			List<DeliveryOrder> list = null;
			if(!map.containsKey(warehouse.getId())){
				list =  new ArrayList<DeliveryOrder>();
				list.add(deliveryOrder);
				map.put(warehouse.getId(),list);
			}else{
				list= map.get(warehouse.getId());
				list.add(deliveryOrder);
			}
		}
		
		
		for (Map.Entry<Integer, List<DeliveryOrder>> entry : map.entrySet()) {
			List<DeliveryOrder> list = entry.getValue();
			String [] idss = new String[list.size()];
			int i = 0;
			for (DeliveryOrder de : list) {
				idss[i] = de.getId().toString();
				i++;
			}
			
			List<File> files = new ArrayList<File>();
			String generice = getExcelFile(webPath, templatePath, idss, true);
			files.add(new File(webPath+"//"+generice));
			//deliveryOrder.getPurchaseOrder().getMaintenanceStation().getEmail()
			//add carbon copy email persons..
			SysParam sysParam = getParamByCode("carbonCopy");
			String []  carbonCopys = null;
			if(sysParam != null){
				if(sysParam.getValue()!= null && !sysParam.getValue().equals("")){
					if(sysParam.getValue().indexOf(',') >= 0 ){
						carbonCopys =sysParam.getValue().split(",");
					}
					else{
						carbonCopys = new String [10];
						carbonCopys[0] = sysParam.getValue();
					}
				}
			}
			
		
			
			mailService.sendMail(list.get(0).getWarehouse().getEmail(),"system","送货清单","送货清单",files,carbonCopys);
		}

		for (String id : ids) {
			DeliveryOrder deliveryOrder = (DeliveryOrder) get(DeliveryOrder.class, Integer.parseInt(id));
			deliveryOrder.setSendEmail(true);
			deliveryOrder.setSendDate(new Date());
			//updateDeliverySendStatus(deliveryOrder);
			if(deliveryOrder.getDeliveryStatus() != null && 
					deliveryOrder.getDeliveryStatus().equals(DeliveryStatus.Generate)){
					deliveryOrder.setSendDate(new Date());
					deliveryOrder.setSendor(SecurityUserHolder.getCurrentUser().getName());
					deliveryOrder.setDeliveryStatus(DeliveryStatus.Send);
			}
			deliveryOrder =  (DeliveryOrder) get(DeliveryOrder.class, Integer.parseInt(id));
			update(deliveryOrder);
		}
//		
//		MaintenanceStation station = deliveryOrder.getPurchaseOrder().getMaintenanceStation();
//		mailService.send(station.getMainWarehouse().getCode(), station.getMainWarehouse().getRegion().getName(),
//				station.getMainWarehouse().getName(),station.getNumber(), station.getName(),
//				deliveryOrder.getPurchaseOrder().getOrderNumber(), deliveryOrder.getNumber(), deliveryOrder.getDeliveryDetails()
//				, station.getMainWarehouse().getEmail(), "system","送货清单", new Date().toLocaleString());
//		deliveryOrder.setSendEmail(true);
//		deliveryOrder.setSendDate(new Date());
//		
//		update(deliveryOrder);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.DeliveryOrderService#updateDeliverySendStatus(com.xbang.attendance.entity.order.
	 * DeliveryOrder)
	 */
	@Override
	public void updateDeliverySendStatus(DeliveryOrder deliveryOrder) throws Exception {
		
//		
//		if(deliveryOrder.getDeliveryStatus() != null && 
//				deliveryOrder.getDeliveryStatus().equals(DeliveryStatus.Sign)){
//			return;
//		}
//		
//		String personStatus = deliveryOrder.getUpdateDeliveryStatusPersonFieldName(DeliveryStatus.Send);
//		String dateStatus = deliveryOrder.getUpdateDeliveryStatusDateFieldName(DeliveryStatus.Send);
//
//		if (null != personStatus && null != dateStatus) {
//			
//			
//			String sqlString  = "update delivery_order set delivery_status= ?,"+personStatus+"= ? ,"+dateStatus+" = ? where id = ?";
////			String sqlSentence = "UPDATE delivery_order SET delivery_status = :devlieryStatus ,  ".concat(personStatus).concat(" = :person , ").concat(
////					dateStatus).concat(" = :date WHERE id = :id");
//
//			Map<String, Object> parameters = new HashMap<String, Object>();
//
//			parameters.put("person", SecurityUserHolder.getCurrentUser().getName());
//			parameters.put("date", new Date());
//			parameters.put("devlieryStatus", deliveryOrder.getDeliveryStatusString(DeliveryStatus.Send));
//			parameters.put("id", deliveryOrder.getId());
//			
//			System.out.println(sqlString);
//			genericDaoSupport.getJdbcTemplate().update(sqlString, new Object[]{
//				SecurityUserHolder.getCurrentUser().getName(),
//				deliveryOrder.getDeliveryStatusString(DeliveryStatus.Send),
//				new Date(),
//				deliveryOrder.getId()
//			});
//			deliveryOrder = (DeliveryOrder) get(DeliveryOrder.class, deliveryOrder.getId());
//			System.out.println(deliveryOrder);
//			//execute(sqlString, parameters);
//		}
		if(deliveryOrder.getDeliveryStatus() != null && 
				deliveryOrder.getDeliveryStatus().equals(DeliveryStatus.Sign)){
			return;
		}
		String personStatus = deliveryOrder.getUpdateDeliveryStatusPersonFieldName(DeliveryStatus.Send);
		String dateStatus = deliveryOrder.getUpdateDeliveryStatusDateFieldName(DeliveryStatus.Send);

		if (null != personStatus && null != dateStatus) {

			String sqlSentence = "UPDATE delivery_order SET delivery_status = :devlieryStatus ,  ".concat(personStatus).concat(" = :person , ").concat(
					dateStatus).concat(" = :date WHERE id = :id");

			Map<String, Object> parameters = new HashMap<String, Object>();

			parameters.put("person", SecurityUserHolder.getCurrentUser().getName());
			parameters.put("date", new Date());
			parameters.put("devlieryStatus", deliveryOrder.getDeliveryStatusString(DeliveryStatus.Send));
			parameters.put("id", deliveryOrder.getId());

			execute(sqlSentence, parameters);
		}
	}

	@Override
	public SysParam getParamByCode(String code) throws Exception {
		StringBuilder sbBuilder = new StringBuilder();
		sbBuilder.append("FROM SysParam s where s.code= ?");
		List list= genericDaoSupport.getHibernateTemplate().find(sbBuilder.toString(),code);
		return  list != null && !list.isEmpty() ? (SysParam)list.get(0) : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.PurchaseOrderService#genericExcelFile(java.lang.String, java.lang.String[])
	 */
	@Override
	public String genericExcelFile(String webPath, String[] fields) throws Exception {
		String genericExcelFile = "DO-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		List orderList = getPurchseOrder(fields);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		
		sheet.setDefaultColumnWidth((short) 20);
		
		HSSFRow row = sheet.createRow(0);// 创建第一行
		
		int i = 0;
		for (FieldUtil.Filed field:FieldUtil.getDeliveryFileds()) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(field.getName());
			i++;
		}
		if (!orderList.isEmpty()) {
			int rowIndex = 1;
			for (Object object : orderList) {
				row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				i = 0;
				for (FieldUtil.Filed field:FieldUtil.getDeliveryFileds()) {
					HSSFCell cell = row.createCell(i);
					cell = row.createCell(i);
					cell.setCellValue(m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
					i++;
				}
			}
		}

		OutputStream os = new FileOutputStream(file);
		workbook.write(os);// 写入
		InputStream in = new FileInputStream(file);
		os.close();
		in.close();
			
		
		return genericExcelFile;
	}

	private List getPurchseOrder(String [] ids) {
		StringBuffer sbBuffer = new StringBuffer("SELECT pro.product_code AS pCode,")
					.append("  pro.product_name AS pName,")
					.append("  SUM(inr.amount) AS amount")
					.append(" FROM inventory_record inr LEFT JOIN warehouse war")
					.append(" ON (war.id = inr.warehouse_id) LEFT JOIN product pro")
					.append("  ON (inr.product_id = pro.id) WHERE (inr.last_inventory_operation = 'OUT'")
					.append("  OR inr.last_inventory_operation = 'TRANSFERRED')")
					.append(" AND inr.delivery_order_id IN ( :ids )")
					.append(" GROUP BY inr.product_id")
					.append(" ORDER BY inr.product_id");
    
		
//							  .append(" ware.name AS wareName,")
//							  .append(" station.number AS stationCode,")
//							  .append(" station.name AS stationName,")
//							  .append(" pro.product_code AS proCode,")
//							  .append(" pro.product_name AS proName,")
//							  .append(" pro.specification AS specification,")
//							  .append(" detail.product_number AS productNumber")
//							  .append(" FROM delivery_order AS delivery")
//							  .append(" LEFT JOIN delivery_details detail")
//							  .append(" ON (delivery.id = detail.delivery_order_id)")
//							  .append(" LEFT JOIN product pro ON (detail.product_id = pro.id)")
//							  .append(" LEFT JOIN maintenance_station station")
//							  .append(" ON (delivery.maintenance_station_id = station.id)")
//							  .append(" LEFT JOIN warehouse ware ON (station.main_warehouse = ware.id)")
//							  .append(" ORDER BY proCode ");
//		
		
		Set<Integer> set = new HashSet<Integer>();
		for (String string : ids) {
			set.add(Integer.valueOf(string));
		}
		logger.info(sbBuffer.toString());
		Map<String,Object> objects = new HashMap<String, Object>();
		objects.put("ids", set);
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuffer.toString(),objects);
	}

	@Override
	public String genericExcelFile(String webPath, String templatePath,String [] ids)throws Exception {
		return getExcelFile(webPath, templatePath, ids, false);
	}
	
	private String getExcelFile(String webPath, String templatePath,String [] ids,Boolean isProtect)throws Exception{
		String genericExcelFile = "DO-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		
		POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(templatePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		
		
		for (int i = 1; i < ids.length; i++) { //  复制同样数量的sheet
			wb.cloneSheet(0);
		}
		
		for (int i = 0; i < ids.length; i++) {
			
			DeliveryOrder deliveryOrder = (DeliveryOrder) get(DeliveryOrder.class, Integer.parseInt(ids[i]));
			MaintenanceStation station = deliveryOrder.getPurchaseOrder().getMaintenanceStation();
			
			HSSFSheet sheet =HSSFUtil.getSheet(i, wb);
			
			if(isProtect){
				SysParam sysParam  = getParamByCode("excelPassword");
				sheet.protectSheet(sysParam.getValue());
			}
			
			
			wb.setSheetName(i,"送货清单-"+deliveryOrder.getNumber());
			
			sheet.setDefaultColumnWidth((short) 20);
			
			sheet.getRow(1).getCell(5).setCellValue(station.getMainWarehouse().getCode());
			sheet.getRow(1).getCell(8).setCellValue(station.getMainWarehouse().getRegion().getName());
			sheet.getRow(2).getCell(5).setCellValue(station.getMainWarehouse().getName());
			sheet.getRow(2).getCell(8).setCellValue(DateUtil.formatDate(deliveryOrder.getGenerateorDate(),DateUtil.DATE_FORMAT));
			sheet.getRow(3).getCell(4).setCellValue(station.getNumber());
			sheet.getRow(3).getCell(7).setCellValue(station.getName());
			sheet.getRow(4).getCell(4).setCellValue(deliveryOrder.getPurchaseOrder().getOrderNumber());
			sheet.getRow(4).getCell(7).setCellValue(deliveryOrder.getNumber());
			
			
			Set<DeliveryDetails> details = deliveryOrder.getDeliveryDetails();
			List<DeliveryDetails> deList = new ArrayList<DeliveryDetails>();
			for (DeliveryDetails deliveryDetails : details) {
				deList.add(deliveryDetails);	
			}
			
			Collections.sort(deList,new Comparator<DeliveryDetails>(){
				@Override
				public int compare(DeliveryDetails o1, DeliveryDetails o2) {
					return o1.getProduct().getProductCode().compareTo(o2.getProduct().getProductCode());
				}
			});
			
			
			int count = 36-7;
			int num = deList.size() - count;
			HSSFCellStyle cellStyle  =  wb.createCellStyle();
			if(num <= 1){
				int j =6;
				for (DeliveryDetails deliveryDetails : deList) {
					sheet.getRow(j).getCell(1).setCellValue(j-5);
					sheet.getRow(j).getCell(2).setCellValue(deliveryDetails.getProduct().getProductCode());
					sheet.getRow(j).getCell(5).setCellValue(deliveryDetails.getProduct().getProductName());
					sheet.getRow(j).getCell(6).setCellValue(deliveryDetails.getProductNumber().doubleValue());
					sheet.getRow(j).getCell(7).setCellValue("");
					j++;
				}
//				cellStyle =  wb.createCellStyle();
//				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//					
//				HSSFRow row = sheet.getRow(36);
//				for (Cell cell2 : row) {
//					cell2.setCellStyle(cellStyle);
//				}
			}
			else{
				int j = 6;
				int len = deList.size();
				
				HSSFRow tempRow = sheet.getRow(33);
			
				int index = 0;
				for (int k =0; k < len;k++) {
					index = k;
					if(j == 36 )
						break;
					sheet.getRow(j).getCell(1).setCellValue(j-5);
					sheet.getRow(j).getCell(2).setCellValue(deList.get(k).getProduct().getProductCode());
					sheet.getRow(j).getCell(5).setCellValue(deList.get(k).getProduct().getProductName());
					sheet.getRow(j).getCell(6).setCellValue(deList.get(k).getProductNumber().doubleValue());
					sheet.getRow(j).getCell(7).setCellValue("");
					j++;
				}
				if(num != 1)
					sheet.shiftRows(36,37,num-1);
				else
					sheet.shiftRows(36,37,num);
				
				HSSFFont cnFont = wb.createFont();  
				cnFont.setFontHeightInPoints((short) 10);  
				cnFont.setFontName("宋体");
				
				for (; index < deList.size();index++,j++) {
				    HSSFRow tmp = sheet.createRow(j);
				    	int k=0;
				    	
					   for (Cell cell2 : tempRow) {
						   if(k == 0){
							   HSSFCellStyle style  =  wb.createCellStyle();
							   style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
							   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
							   style.setBorderTop(HSSFCellStyle.BORDER_NONE);
							   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
							   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
							   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
							   style.setFont(cnFont);
								Cell  cell3 =tmp.createCell(k);
					   			HSSFUtil.copyCellAndStyle(cell3, cell2,style);
							   k++;
							   continue;
						   }
						   
							
						   	Cell  cell3 =tmp.createCell(k);
						   	if(k != 5){
							   		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
									cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
									cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED);
									cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
									cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
									cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
									cellStyle.setFont(cnFont);
						   			HSSFUtil.copyCellAndStyle(cell3, cell2,cellStyle);
						   	}
						   	if(k ==1 )
						   	{
						   		cell3.setCellValue(j-5);
						   	}else if( k == 2){
						   		cell3.setCellValue(deList.get(index).getProduct().getProductCode());
						   	}
						   	else if(k == 5){
						   		HSSFCellStyle style  =  wb.createCellStyle();
						   		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
						   		style.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
						   		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
								style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);
								style.setBorderRight(HSSFCellStyle.BORDER_THIN);
								style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
								style.setFont(cnFont);
						   		cell3.setCellStyle(style);
						   		cell3.setCellValue(deList.get(index).getProduct().getProductName());
						   	}else if(k == 6)
						   	{
						   		cell3.setCellValue(deList.get(index).getProductNumber().doubleValue());
						   	}else if(k == 7) {
						   		cell3.setCellValue("");
							}
						   	k++;
					   }
					sheet.addMergedRegion(new Region(j,(short)2,j,(short)4));
				   	sheet.addMergedRegion(new Region(j,(short)7,j,(short)8));
				   	
				    HSSFRow t =HSSFUtil.getRow(36, sheet);
				    HSSFRow t2 = HSSFUtil.getRow(35, sheet);
				    
				    cellStyle =  wb.createCellStyle();
					cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
					cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderTop(HSSFCellStyle.BORDER_DASHED);
					cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
					cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
					cellStyle.setFont(cnFont);
					for (Cell cell2 : t) {
						cell2.setCellStyle(cellStyle);
					}
					for (Cell cell2 : t2) {
						cell2.setCellStyle(cellStyle);
					}
					
					HSSFCellStyle style  =  wb.createCellStyle();
			   		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			   		style.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
			   		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);
					style.setBorderRight(HSSFCellStyle.BORDER_THIN);
					style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
					style.setFont(cnFont);
					Cell c1 = t.getCell(5);
					c1.setCellStyle(style);
					Cell c2 = t2.getCell(5);
					c2.setCellStyle(style);
				  }
//				cellStyle =  wb.createCellStyle();
//				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//					
//				HSSFRow row = sheet.getRow(36+num-1);
//				for (Cell cell2 : row) {
//					cell2.setCellStyle(cellStyle);
//				}
			}
		}		   
		OutputStream os = new FileOutputStream(file);
		wb.write(os);
		os.close();  
		return genericExcelFile;
	}

	
	public void updateStatus(String[] ids) throws Exception {
		//StringBuilder sbBuilder = new StringBuilder("FROM DeliveryOrder deliveryOrder where deliveryOrder.id in( ? )");
		//List<DeliveryStatus>;
		
		Set<Integer> idsSet = new HashSet<Integer>();
		
		String personStatus = DeliveryOrder.getUpdateDeliveryStatusPersonFieldName(DeliveryStatus.Sign);
		String dateStatus = DeliveryOrder.getUpdateDeliveryStatusDateFieldName(DeliveryStatus.Sign);
		
		for(String id : ids){
			idsSet.add(Integer.valueOf(id));
		}
		Map<String,Object> objects = new HashMap<String, Object>();
		String sqlSentence = "UPDATE delivery_order SET delivery_status = :deliveryStatus ,  ".concat(personStatus).concat(" = :personStatus , ").concat(
				dateStatus).concat(" = :dateStatus  WHERE id in( :ids )");
		objects.put("deliveryStatus", DeliveryStatus.Sign.toString());
		objects.put("personStatus", SecurityUserHolder.getCurrentUser().getUsername());
		objects.put("dateStatus", new Date());
		objects.put("ids", idsSet);
		genericDaoSupport.getJdbcTemplate().update(sqlSentence,objects);
		
//		sqlSentence = "UPDATE delivery_order SET delivery_date = '" + DateUtils.format(new Date())  + "' WHERE id = ?";
//		genericDaoSupport.getJdbcTemplate().update(sqlSentence,new Object[]{57});
//		System.out.println("111");
	}
	
}
 